﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess.IService;
using System.Data;
using Utility;
using System.Collections;
using System.Data.SqlClient;
using Entity;
namespace DataAccess.Service
{
    public class PaymentItemService : IPaymentItemService
    {
        //5个
        
        //根据学号，和项目号得到学生个人缴费明细
        public DataSet selectStudentItemDS(List<int> projectId,string studentId)
        {
            StringBuilder strBuilder = new StringBuilder();
            for (int i = 0; i < projectId.Count; i++)
            {
                String commandText = "select * from StudentPaymentProjectDetail,PaymentProjectDetail,PaymentProject,StudentPaymentProject where StudentPaymentProjectDetail.detailId=PaymentProjectDetail.detailId and StudentPaymentProjectDetail.stuProjectId=StudentPaymentProject.stuProjectId and PaymentProjectDetail.projectId=StudentPaymentProject.projectId and StudentPaymentProject.projectId=PaymentProject.projectId and  StudentPaymentProjectDetail.stuProjectId ='" + projectId[i] + "';";
                strBuilder.Append(commandText);
            }
            return SQLHelper.ExecuteDataSet(strBuilder.ToString());
        }


        public void insertPaymentProjectDetail(List<PaymentProjectDetail> list)
        {
            string commandText = string.Format("Insert into PaymentProjectDetail(projectId,detailName,detailAmount,seperate) values(@projectId,@detailName,@detailAmount,@seperate)");
            for (int i = 0; i < list.Count; i++)
            {
                SqlParameter[] paras = new SqlParameter[]
                {
                    new SqlParameter("projectId", list.ElementAt(i).PaymentProjectId),
                    new SqlParameter("detailName", list.ElementAt(i).ProDetailName),
                    new SqlParameter("detailAmount", list.ElementAt(i).ProDetailAmount),
                    new SqlParameter("seperate", list.ElementAt(i).SeperatePay)
                };
                SQLHelper.ExecuteNonQuery(commandText, paras);
            }
        }

        public int selectMaxProjectDetailId()
        {
            int result = 0;
            string commandText = string.Format("select Max(detailId) from PaymentProjectDetail");
            result = (int)SQLHelper.ExecuteScalar(commandText);
            return result;
        }

        public void insertStudentPaymentItem(PaymentProject project, List<String> stuProjectIds)
        {
            string commandText = string.Format("Insert into StudentPaymentProjectDetail(detailId,amountShPay,amountFcPay,detailStatus,stuProjectId) values(@detailId,@amountShPay,@amountFcPay,@detailStatus,@stuProjectId)");
                for (int i = 0; i < stuProjectIds.Count; i++)
                {
                    for (int j = 0; j < project.ProjectDetailList.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[]
                        {
                           new SqlParameter("detailId", project.ProjectDetailList[j].PayProjectDetailId),
                           new SqlParameter("amountShPay", project.ProjectDetailList[j].ProDetailAmount),
                           new SqlParameter("amountFcPay", float.Parse("0")),
                           new SqlParameter("detailStatus", "未付款"),
                           new SqlParameter("stuProjectId", stuProjectIds[i])
                        };
                        SQLHelper.ExecuteNonQuery(commandText, paras);
                    }
                }
    
        }



        public bool UpdateStudentPaymentDetail(List<OrderDetail> details)
        {

            bool sucess = false;
            string commandText = string.Format("update StudentPaymentProjectDetail  set amountShPay=amountShPay-@amount,amountFcPay=amountFcPay+@amount  where stuProjectId=@stuProjectId and detailId=@detailId");
            for (int i = 0; i < details.Count; i++)
            {
                  SqlParameter[] paras = new SqlParameter[]
                 {
                   new SqlParameter("amount", details[i].Amount),
                   new SqlParameter("detailId", details[i].DetailId),
                   new SqlParameter("stuProjectId", details[i].ProjectId)
                 };
                  SQLHelper.ExecuteNonQuery(commandText, paras);  
             }
                //在数据库中写一个函数，更新两表的状态
            sucess = true;
            return sucess;

        }



        public bool updateStudentPaymentDetail(OrderDetail detail)
        {

            bool sucess = false;
            string commandText = string.Format("update StudentPaymentProjectDetail set amountShPay=amountShPay+@amount,amountFcPay=amountFcPay-@amount  where stuProjectId=@stuProjectId and detailId=@detailId");
            
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("amount", detail.Amount),
                new SqlParameter("detailId", detail.DetailId),
                new SqlParameter("stuProjectId", detail.ProjectId)
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras)>0)
            {
                sucess = true;
            }
            return sucess;
        }





        public DataSet selectStudentItemDs(int projectId,String stuId)
        {
            String commandText = "select * from StudentPaymentProjectDetail,PaymentProjectDetail,PaymentProject,StudentPaymentProject where StudentPaymentProjectDetail.detailId=PaymentProjectDetail.detailId and StudentPaymentProjectDetail.stuProjectId=StudentPaymentProject.stuProjectId and PaymentProjectDetail.projectId=StudentPaymentProject.projectId and StudentPaymentProject.projectId=PaymentProject.projectId and  StudentPaymentProject.projectId ='"+projectId+"' and StudentPaymentProject.stuId='"+stuId+"'";
            return SQLHelper.ExecuteDataSet(commandText);
        }



        public DataSet getStudentUnPaidDetailItems(String stuId)
        {
            String commandText = "select * " +
    "from StudentPaymentProject,StudentPaymentProjectDetail,PaymentProject,PaymentProjectDetail " +
    "where StudentPaymentProject.projectId = PaymentProject.projectId " +
        "and StudentPaymentProjectDetail.stuProjectId = StudentPaymentProject.stuProjectId " +
        "and PaymentProjectDetail.projectId = PaymentProject.projectId " +
        "and StudentPaymentProjectDetail.detailId = PaymentProjectDetail.detailId " +
        "and StudentPaymentProjectDetail.detailStatus = '未缴清' " +
        "and StudentPaymentProject.stuId = @stuID";
            SqlParameter[] paras = new SqlParameter[]
            {
                    new SqlParameter("@stuID", stuId)
            };
            return SQLHelper.ExecuteDataSet(commandText, CommandType.Text,paras);
        }







        public int getStudentProjectIdByDetailId(int detailId,string stuId)
        {
            int result = 0;
            string commandText = string.Format("select StudentPaymentProject.stuProjectId    " +
    "from StudentPaymentProjectDetail,StudentPaymentProject  " +
    "where StudentPaymentProject.stuProjectId = StudentPaymentProjectDetail.stuProjectId  " +
        "and detailId= @detailId  " +
        "and stuId = @stuId");
            SqlParameter[] paras = new SqlParameter[]
            {
                    new SqlParameter("@detailId", detailId),
                    new SqlParameter("@stuId", stuId),
            };
            result = (int)SQLHelper.ExecuteScalar(commandText,CommandType.Text,paras);
            return result;
        }


        public DataSet getStudentProjectDetailInfoByDetailId(int detailId)
        {
            String commandText = "select * " +
    "from StudentPaymentProject,StudentPaymentProjectDetail,PaymentProject,PaymentProjectDetail " +
	"where StudentPaymentProject.projectId = PaymentProject.projectId " +
		"and StudentPaymentProjectDetail.stuProjectId = StudentPaymentProject.stuProjectId " +
		"and PaymentProjectDetail.projectId = PaymentProject.projectId " +
        "and StudentPaymentProjectDetail.detailId = PaymentProjectDetail.detailId " +
        "and StudentPaymentProjectDetail.detailId = @detailId";
            SqlParameter[] paras = new SqlParameter[]
            {
                    new SqlParameter("@detailId", detailId)
            };
            return SQLHelper.ExecuteDataSet(commandText, CommandType.Text, paras);
        }
    }
}
